package org.jeecg.modules.htxincailiao.analytics.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.jeecg.modules.htxincailiao.analytics.domain.vo.HTGoodsDetailVisitInfoVO;
import org.jeecg.modules.htxincailiao.analytics.domain.vo.HTGoodsDetailVisitStatisticsVO;
import org.jeecg.modules.htxincailiao.analytics.entity.HTGoodsDetailVisitAction;

import java.util.List;

/**
 * @Description: ht_goods_detail_visit_action
 * @Author: jeecg-boot
 * @Date: 2022-06-07 10:12:00
 * @Version: V1.0
 */
public interface HTGoodsDetailVisitActionMapper extends BaseMapper<HTGoodsDetailVisitAction> {

    @Select("<script>" +
            "SELECT DATE_FORMAT(visit_time,<![CDATA[ '%Y-%m-%d %H') ]]> AS time_interval, COUNT(visit_ip) AS pv, " +
            "COUNT" +
            "(distinct visit_ip) AS uv " +
            "FROM ht_goods_detail_visit_action " +
            "<![CDATA[ WHERE visit_time > #{beginTime} AND visit_time < #{endTime} ]]>" +
            "GROUP BY time_interval" +
            "</script>")
    List<HTGoodsDetailVisitInfoVO> queryPVUVData(@Param("beginTime") String beginTime,
                                                 @Param("endTime") String endTime);


    @Select("<script>" +
            " SELECT " +
            " CONVERT((T.add_to_cart_click_time_range_count / T.pv_time_range_count),DECIMAL(15, 2))" +
            " AS cart_click_through_time_range_rate, " +
            " CONVERT((T.immediately_purchase_click_time_range_count / T.pv_time_range_count),DECIMAL(15, 2))" +
            " AS immediately_purchase_click_through_time_range_rate, " +
            " CONVERT((T.immediately_purchase_click_time_range_count / T.pv_time_range_count),DECIMAL(15, 2))" +
            " AS turnover_ratio_time_range_rate, " +
            " CONVERT((T.add_to_cart_click_count / T.pv_total_count),DECIMAL(15, 2))" +
            " AS cart_click_through_rate, " +
            " CONVERT((T.immediately_purchase_click_count / T.pv_total_count),DECIMAL(15, 2))" +
            " AS immediately_purchase_click_through_rate, " +
            " CONVERT((T.immediately_purchase_click_count / T.pv_total_count),DECIMAL(15, 2))" +
            " AS turnover_ratio_rate " +
            " FROM ( " +
            " SELECT  " +
            " (SELECT COUNT(*) FROM ht_goods_detail_visit_action" +
            " WHERE visit_time <![CDATA[ > ]]> #{beginTime} " +
            " AND visit_time <![CDATA[ < ]]>  #{endTime} )" +
            " AS pv_time_range_count, " +
            " (SELECT COUNT(add_to_cart_click_count )" +
            " FROM ht_goods_detail_visit_action" +
            " WHERE add_to_cart_click_count <![CDATA[ > ]]> 0 " +
            " AND visit_time <![CDATA[ > ]]> #{beginTime} " +
            " AND visit_time <![CDATA[ < ]]> #{endTime} )" +
            " AS add_to_cart_click_time_range_count, " +
            " (SELECT COUNT(immediately_purchase_click_count )" +
            " FROM ht_goods_detail_visit_action" +
            " WHERE immediately_purchase_click_count <![CDATA[ > ]]> 0" +
            " AND visit_time <![CDATA[ > ]]> #{beginTime}" +
            " AND visit_time <![CDATA[ < ]]> #{endTime} )" +
            " AS immediately_purchase_click_time_range_count, " +
            " (SELECT COUNT(*) FROM ht_order" +
            " WHERE create_time <![CDATA[ > ]]> #{beginTime}" +
            " AND create_time  <![CDATA[ < ]]> #{endTime} )" +
            " AS order_time_range_count, " +
            " (SELECT COUNT(*) FROM ht_goods_detail_visit_action) AS pv_total_count, " +
            " (SELECT COUNT(add_to_cart_click_count) FROM ht_goods_detail_visit_action" +
            " WHERE add_to_cart_click_count <![CDATA[ > ]]> 0) AS add_to_cart_click_count, " +
            " (SELECT COUNT(immediately_purchase_click_count ) FROM ht_goods_detail_visit_action" +
            " WHERE immediately_purchase_click_count <![CDATA[ > ]]> 0 )" +
            " AS immediately_purchase_click_count, " +
            " (SELECT COUNT(*) FROM ht_order )" +
            " AS order_count ) as T" +
            "</script>")
    HTGoodsDetailVisitStatisticsVO queryStatistics(@Param("beginTime") String beginTime,
                                                   @Param("endTime") String endTime);
}
